package com.nova.edc.service.system.impl;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import com.nova.edc.model.system.Group;
import com.nova.edc.model.system.Login;
import com.nova.edc.model.util.CountModel;
import com.nova.edc.model.util.Location;
import com.nova.edc.service.system.GroupService;
import com.nova.frame.dao.criterion.MatchMode;
import com.nova.module.sql.SqlBuffer;
import com.seio.edc.util.BmapUtil;
import com.seio.edc.util.IntegerUtils;
import org.springframework.beans.factory.annotation.Autowired;

import com.nova.edc.dao.system.CustomerInfoDao;
import com.nova.edc.model.system.CustomerInfo;
import com.nova.edc.model.system.ModelConstant;
import com.nova.edc.service.system.CustomerInfoService;
import com.nova.frame.dao.buzz.query.Order;
import com.nova.frame.dao.buzz.query.Query;
import com.nova.frame.dao.criterion.Restrictions;
import com.nova.frame.dao.pager.Page;
import com.nova.frame.dao.pager.PageList;
import com.nova.frame.utils.JdbcUtils;
import com.nova.frame.utils.StringUtils;
import com.nova.module.filter.query.Filter;

public class CustomerInfoServiceImpl implements CustomerInfoService {

    @Autowired
    private CustomerInfoDao novaCustomerInfoDao;
    @Autowired
    private GroupService groupService;

    @Override
    public List<CustomerInfo> findAllCustomerInfo() {
        return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0))
                .list();
    }

    @Override
    public List<CustomerInfo> findCustomerInfoByGroupAndLogin(
            Integer groupCode, String username) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query()
                .add(Restrictions.eq("Scrapped", 0))
                .add(Restrictions.eq("AddBy", username));
        query.add(Restrictions.eqOrIsNull("GroupCode", groupCode));
        return query.list();
    }

    @Override
    public List<CustomerInfo> findCustomerInfoByLogin(String username) {
        return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0))
                .add(Restrictions.eq("AddBy", username)).list();
    }

    @Override
    public CustomerInfo insert(CustomerInfo customerInfo) {
        CustomerInfo temp = getByCompanyCode(customerInfo.getCompanycode());
        if (temp == null) {
            return novaCustomerInfoDao.insert(customerInfo);
        } else {
            return temp;
        }

    }

    @Override
    public CustomerInfo insertByExcel(CustomerInfo customerInfo) {
        if (customerInfo.getStreet() != null && !"".equals(customerInfo.getStreet())) {
            Location location = BmapUtil.getLocation(customerInfo);
            if (location != null) {
                customerInfo.setLng(location.getLng());
                customerInfo.setLat(location.getLat());
            }
        }
        return novaCustomerInfoDao.insert(customerInfo);
    }

    @Override
    public int update(CustomerInfo customerInfo) {
        return novaCustomerInfoDao.updateNotNull(customerInfo);
    }

    @Override
    public PageList<CustomerInfo> findCustomerInfoPageList(
            CustomerInfo customerInfo, Page page, String sidx, String sord) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query()
                .add(Restrictions.eq("Scrapped", 0))
                .addOrder(Order.custom(sord, sidx));
        Filter.addFilterCondition(query, customerInfo);
        return query.pageList(page);
    }

    @Override
    public CustomerInfo getById(int id) {
        return novaCustomerInfoDao.getById(id);
    }

    @Override
    public int deleteById(int id) {
        CustomerInfo customerInfo = novaCustomerInfoDao.getById(id);
        customerInfo.setScrapped(1);
        return novaCustomerInfoDao.update(customerInfo);
    }

    @Override
    public boolean isExistCompanyCode(Integer companyCode, Integer customerinfoid) {
        Query<CustomerInfo> query = novaCustomerInfoDao
                .query()
                .add(Restrictions.eq("t_customerinfo.CompanyCode", companyCode))
                .add(Restrictions.eq("t_customerinfo.Scrapped", 0));
        if (customerinfoid != null && customerinfoid != 0) {
            query.add(Restrictions.ne("t_customerinfo.ID", customerinfoid));
        }
        int count = query.list().size();
        return count == 0;
    }

    @Override
    public CustomerInfo getByCompanyCode(int companyCode) {
        return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0))
                .add(Restrictions.eq("CompanyCode", companyCode)).get();
    }

    @Override
    public PageList<CustomerInfo> findCustomerInfoPageListByRole(Login login, Integer companyCode,
                                                                 String customerName, Integer customerinfotype, Page page, String sidx, String sord) {
        if (StringUtils.isNotNullOrEmpty(customerName)) {
            customerName = customerName.trim();
        }
        PageList<CustomerInfo> customers = new PageList<CustomerInfo>();
        if (login.getRoletype() != null && login.getRoletype() == ModelConstant.ROLE_GROUP) {
            SqlBuffer sqlBuffer = new SqlBuffer("SELECT * FROM (SELECT * FROM t_customerinfo WHERE GroupCode = ? AND CustomerName LIKE ? ORDER BY CompanyCode )detail");
            if (IntegerUtils.isEmptyAndNull(companyCode)) {
                sqlBuffer.appendAnd("detail.CompanyCode", companyCode);
            }
            int count = JdbcUtils.count(sqlBuffer.getCountSql(), login.getGroupcode(), "%" + (customerName == null ? "" : customerName) + "%").intValue();
            List<CustomerInfo> customerInfos = JdbcUtils.query_limit(CustomerInfo.class, sqlBuffer.getSelectSql(), page, login.getGroupcode(), "%" + (customerName == null ? "" : customerName) + "%");
            page.make(count);
            customers.setDatas(customerInfos);
            customers.setPage(page);
        } else {
            SqlBuffer sqlBuffer = new SqlBuffer(" SELECT * FROM ((SELECT t_customerinfo.* FROM t_customerinfo "
                    + "WHERE Scrapped = 0 AND t_customerinfo.addby = ? AND t_customerinfo.CustomerName LIKE ? ) "
                    + "UNION (SELECT t_customerinfo.* FROM t_login LEFT JOIN t_customerinfo "
                    + "ON t_login.CustomerInfoID = t_customerinfo.ID "
                    + "WHERE t_login.Scrapped = 0 AND t_customerinfo.Scrapped = 0 AND t_login.UserName = ? AND t_customerinfo.CustomerName LIKE ? ) "
                    + "ORDER BY ADDDATE, CustomerinfoType DESC,ID ASC) mydetail", false);
            if (IntegerUtils.isEmptyAndNull(customerinfotype)) {
                sqlBuffer.appendAnd("mydetail.CustomerinfoType", customerinfotype);
            }
            if (IntegerUtils.isEmptyAndNull(companyCode)) {
                sqlBuffer.appendAnd("mydetail.CompanyCode", companyCode);
            }
            if (StringUtils.isNotNullOrEmpty(customerName)) {
                customerName = customerName.trim();
            }
            int rowCount = JdbcUtils.count(sqlBuffer.getCountSql(),
                    login.getUsername(), "%" + (customerName == null ? "" : customerName) + "%",
                    login.getUsername(), "%" + (customerName == null ? "" : customerName) + "%").intValue();
            List<CustomerInfo> collectAreas = JdbcUtils.query_limit(
                    CustomerInfo.class, sqlBuffer.getSelectSql(), page,
                    login.getUsername(), "%" + (customerName == null ? "" : customerName) + "%",
                    login.getUsername(), "%" + (customerName == null ? "" : customerName) + "%");

            page.make(rowCount);
            customers.setDatas(collectAreas);
            customers.setPage(page);
        }
        return customers;
    }

    @Override
    public List<CustomerInfo> findCustomerInfoListByRole(String addBy,
                                                         String customerName, String sidx, String sord) {
        String sql = " (SELECT t_customerinfo.* FROM t_customerinfo "
                + "WHERE Scrapped = 0 AND t_customerinfo.addby = ? AND t_customerinfo.CustomerName LIKE ?) "
                + "UNION (SELECT t_customerinfo.* FROM t_login LEFT JOIN t_customerinfo "
                + "ON t_login.CustomerInfoID = t_customerinfo.ID "
                + "WHERE t_login.Scrapped = 0 AND t_customerinfo.Scrapped = 0 AND t_login.UserName = ? AND t_customerinfo.CustomerName LIKE ?) "
                + "ORDER BY ADDDATE ASC";
        if (StringUtils.isNotNullOrEmpty(customerName)) {
            customerName = customerName.trim();
        }
        List<CustomerInfo> result = JdbcUtils.query(CustomerInfo.class, sql,
                addBy, "%" + (customerName == null ? "" : customerName) + "%",
                addBy, "%" + (customerName == null ? "" : customerName) + "%");
        return result;
    }

    @Override
    public PageList<CustomerInfo> findPaymentPageList(String addBy,
                                                      Integer year, Integer month, String customerName, Page page) {
        String sql = " select tc.*,IFNULL(tp.status,"
                + ModelConstant.PAY_NOTPAY
                + ") status from t_customerinfo tc"
                + " left join t_payment tp on"
                + " tc.CompanyCode=tp.CompanyCode"
                + " and tp.Year=?"
                + " and tp.Month=?"
                + " where tc.AddBy=? and tc.CustomerName like ? AND tc.Scrapped =0 ORDER BY STATUS,LastPayDate";
        PageList<CustomerInfo> customers = new PageList<CustomerInfo>();
        if (StringUtils.isNotNullOrEmpty(customerName)) {
            customerName = customerName.trim();
        }
        List<CustomerInfo> result = JdbcUtils.query(CustomerInfo.class, sql,
                year, month, addBy, "%"
                        + (customerName == null ? "" : customerName) + "%");
        customers.setDatas(result);
        page.make(result.size());
        customers.setPage(page);
        return customers;
    }

    @Override
    public CustomerInfo getByIdused(int id) {
        return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0))
                .add(Restrictions.eq("ID", id)).get();
    }

    @Override
    public List<CustomerInfo> findCustomerByCity(Login login, String city) {
        if (login.getRoletype() != null && login.getRoletype() == ModelConstant.ROLE_GROUP) {
            return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0)).add(Restrictions.ne("Lng", ""))
                    .add(Restrictions.eq("GroupCode", login.getGroupcode())).add(Restrictions.or(Restrictions.eq("City", city), Restrictions.eq("District", city))).list();
        } else {
            return novaCustomerInfoDao.query().add(Restrictions.eq("Scrapped", 0)).add(Restrictions.ne("Lng", ""))
                    .add(Restrictions.eq("AddBy", login.getUsername())).add(Restrictions.or(Restrictions.eq("City", city), Restrictions.eq("District", city))).list();
        }
    }

    @Override
    public void insertByExcel(List<CustomerInfo> list, Login login) {
        for (int i = 0; i < list.size(); i++) {
            CustomerInfo customerInfo = list.get(i);
            customerInfo.setAddby(login.getUsername());
            // 设置公司的集团信息
            String groupname = customerInfo.getGroupname();
            if (StringUtils.isNotNullOrEmpty(groupname)) {
                Group group = groupService.getByGroupName(groupname);
                if (group != null) {
                    customerInfo.setGroupcode(group.getGroupcode());
                }
                Location location = BmapUtil.getLocation(customerInfo);
                if (location != null) {
                    customerInfo.setLng(location.getLng());
                    customerInfo.setLat(location.getLat());
                }
            }
            insert(customerInfo);
        }
    }

    @Override
    public List<String> findAllCusomerName() {
        String sql = "SELECT CustomerName FROM t_customerinfo";
        List<CustomerInfo> list = JdbcUtils.query(CustomerInfo.class, sql);
        List<String> strings = new ArrayList<String>();
        for (int i = 0; i < list.size(); i++) {
            strings.add(list.get(i).getCustomername());
        }
        return strings;
    }

    @Override
    public int findIdByName(String name) {
        CustomerInfo customerInfo = novaCustomerInfoDao.query().add(Restrictions.eq("CustomerName", name)).get();
        return customerInfo.getId();
    }

    @Override
    public List<String> findAllProvince() {
        String sql = "SELECT DISTINCT  Province " +
                "FROM t_customerinfo WHERE Province IS NOT NULL";
        List<String> list = JdbcUtils.query(String.class, sql);
        return list;
    }

    @Override
    public List<String> findCitysByProvince(String province) {
        String sql = "SELECT DISTINCT  City " +
                "FROM t_customerinfo WHERE Province = ? AND City IS NOT NULL";
        List<String> list = JdbcUtils.query(String.class, sql, province.trim());
        return list;
    }

    @Override
    public List<String> findDistrictsByCity(String city) {
        String sql = "SELECT DISTINCT  District " +
                "FROM t_customerinfo WHERE City = ? AND District IS NOT NULL";
        List<String> list = JdbcUtils.query(String.class, sql, city.trim());
        return list;
    }

    @Override
    public PageList<CustomerInfo> findByCountModelPagelist(Page page, CountModel countModel, Login login) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        if (login.getRoletype() != null && login.getRoletype() == ModelConstant.ROLE_GROUP) {
            query.add(Restrictions.eq("t_customerinfo.GroupCode", login.getGroupcode()));
        }
        if (StringUtils.isNotNullOrEmpty(countModel.getProvince())) {
            query.add(Restrictions.like("t_customerinfo.Province", countModel.getProvince().trim()));
        }
        if (StringUtils.isNotNullOrEmpty(countModel.getCity())) {
            query.add(Restrictions.like("t_customerinfo.City", countModel.getCity().trim()));
        }
        if (StringUtils.isNotNullOrEmpty(countModel.getDistrict())) {
            query.add(Restrictions.like("t_customerinfo.District", countModel.getDistrict().trim()));
        }
        if (StringUtils.isNotNullOrEmpty(countModel.getPersonel())) {
            if (countModel.getPersonel().indexOf("-") > 0) {
                String[] split = countModel.getPersonel().split("-");
                query.add(Restrictions.ge("t_customerinfo.EmployeeNum", split[0]));
                query.add(Restrictions.le("t_customerinfo.EmployeeNum", split[1]));
            } else {
                String[] split = countModel.getPersonel().split(ModelConstant.MAXIMAL_SIGH);
                query.add(Restrictions.ge("t_customerinfo.EmployeeNum", split[0]));
            }
        }
        if (StringUtils.isNotNullOrEmpty(countModel.getAcreage())) {
            if (countModel.getAcreage().indexOf("-") > 0) {
                String[] split = countModel.getAcreage().split("-");
                query.add(Restrictions.ge("t_customerinfo.Square", split[0]));
                query.add(Restrictions.le("t_customerinfo.Square", split[1]));
            } else {
                String[] split = countModel.getAcreage().split(ModelConstant.MAXIMAL_SIGH);
                query.add(Restrictions.ge("t_customerinfo.EmployeeNum", split[0]));
            }
        }
        return query.pageList(page);
    }

    @Override
    public List<CustomerInfo> findByGroupcode(Integer groupcode) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        query.add(Restrictions.eq("GroupCode", groupcode));
        query.addOrder(Order.asc("t_customerinfo.CustomerName"));
        return query.list();
    }

    @Override
    public List<Integer> getCustomerinfoId(List<CustomerInfo> list) {
        List<Integer> arrayList = new ArrayList<Integer>();
        for (CustomerInfo info : list) {
            arrayList.add(info.getId());
        }
        return arrayList;
    }

    @Override
    public CustomerInfo testInsert(CustomerInfo customerInfo) {
        CustomerInfo info = novaCustomerInfoDao.query().add(Restrictions.eq("CompanyCode", customerInfo.getCompanycode())).get();
        if (info != null) {
            info.setScrapped(0);
            update(info);
            return getById(info.getId());
        } else {
            return insert(customerInfo);
        }
    }

    @Override
    public List<CustomerInfo> findByCodeName(Integer companyCode, String customerName) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        if (IntegerUtils.isEmptyAndNull(companyCode)) {
            query.add(Restrictions.eq("CompanyCode", companyCode));
        }
        if (StringUtils.isNotNullOrEmpty(customerName)) {
            query.add(Restrictions.like("CustomerName", customerName, MatchMode.ANYWHERE));
        }
        return query.list();
    }

    @Override
    public List<CustomerInfo> findByCodeNameLike(CustomerInfo customerInfo) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        if (IntegerUtils.isEmptyAndNull(customerInfo.getCompanycode())) {
            query.add(Restrictions.like("CompanyCode", customerInfo.getCompanycode().toString(), MatchMode.ANYWHERE));
        }
        if (StringUtils.isNotNullOrEmpty(customerInfo.getCustomername())) {
            query.add(Restrictions.like("CustomerName", customerInfo.getCustomername(), MatchMode.ANYWHERE));
        }
        return query.list();
    }

    @Override
    public List<CustomerInfo> findByProvince(String province) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        query.add(Restrictions.like("t_customerinfo.Province", province.trim()));
        return query.list();
    }

    @Override
    public List<Map<String, Object>> findAreaNum() {
        String sql = "SELECT name, max(value) value " +
                "FROM ( " +
                "       SELECT ShowName name, count(*) value FROM t_customerinfo LEFT JOIN t_map_area ON t_map_area.NAME = Province WHERE Province IS NOT NULL GROUP BY Province " +
                "       UNION " +
                "       SELECT City name, count(*) value FROM t_customerinfo WHERE City IS NOT NULL GROUP BY City " +
                "       UNION " +
                "       SELECT  District name, count(*) value FROM t_customerinfo WHERE City IS NOT NULL GROUP BY District " +
                "     ) area GROUP BY name";
        List<Map<String, Object>> maps = JdbcUtils.queryMap(sql);
        return maps;
    }

    @Override
    public List<CustomerInfo> findByGroupProvince(Integer groupcode, String province) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        query.add(Restrictions.eq("GroupCode", groupcode));
        query.add(Restrictions.like("t_customerinfo.Province", province.trim()));
        return query.list();
    }

    @Override
    public List<CustomerInfo> findByGroupName(Integer groupcode, String customerName) {
        Query<CustomerInfo> query = novaCustomerInfoDao.query();
        query.add(Restrictions.like("CustomerName", customerName, MatchMode.ANYWHERE))
                .add(Restrictions.eq("GroupCode", groupcode));
        return query.list();
    }
}
